﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
using Utility;
namespace Service
{
    /// <summary>
    /// 功能：HW_District类数据访问
    /// 创建人：Wilson
    /// 创建时间：2013-1-30   
    /// </summary>
    public class HW_DistrictService : IDisposable
    {
        #region 公共
        private System.ComponentModel.Component component = new System.ComponentModel.Component();

        private bool disposed = false;

        ~HW_DistrictService()
        {
            Dispose(false);
        }

        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (!this.disposed)
            {

                if (disposing)
                {

                    component.Dispose();
                }

                disposed = true;

            }
        }
        #endregion
		#region##MapperHW_District
        public IList<HW_District> MapperList(SqlDataReader reader)
         {
            IList<HW_District> list = new List<HW_District>();
            HW_District obj=null;
            while (reader.Read())
            {
                obj = new HW_District();
                obj.DistrictCode = (string)reader["DistrictCode"];
                obj.Name = (string)reader["Name"];
                obj.Sort = reader["Sort"] as int?;
                obj.ParentDistrictCode = reader["ParentDistrictCode"] as string;
                obj.Level = (int)reader["Level"];
                obj.SchoolNumber = reader["SchoolNumber"] as int?;
                list.Add(obj);
            }
            return list;
         }
        public HW_District Mapper(SqlDataReader reader)
         {
            HW_District obj=null;
            if (reader.Read())
            {
                obj = new HW_District();
                obj.DistrictCode = (string)reader["DistrictCode"];
                obj.Name = (string)reader["Name"];
                    obj.Sort = reader["Sort"] as int?;
                obj.ParentDistrictCode = reader["ParentDistrictCode"] as string;
                obj.Version = (int)reader["Version"];
                obj.Level = (int)reader["Level"];
                    obj.SchoolNumber = reader["SchoolNumber"] as int?;
            }
            return obj;
         }
        #endregion
        #region##通过主键ID得到HW_District
        /// <summary>
        /// 功能：通过ID得到HW_District
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
        public HW_District GetById(string districtcode)
        {            
            string sql = "select * from HW_District where DistrictCode = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",districtcode)
            };
            
            SqlDataReader reader=SqlHelper.ExecuteReader(WebConn.connString,CommandType.Text,sql,sp);
            return Mapper(reader);
        }
        public DataTable GetManager(string districtcode)
        {
            string sql = "select * from HW_Manger_Admin_User where districtcode=@districtcode";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@districtcode",districtcode)
            };

            DataTable dt = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            return dt;
        }
        #endregion
		
		#region##通过主键ID删除HW_District
        /// <summary>
        /// 功能：通过主键ID删除HW_District
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(string districtcode)
		{
			int i = 0;
			string sql = "delete from HW_District where DistrictCode = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",50)
            };
            i= SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
			return i;
		}
        /// <summary>
        /// 功能：通过主键ID删除HW_District
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(SqlTransaction transaction,string districtcode)
		{
			int i = 0;
			string sql = "delete from HW_District where DistrictCode = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",50)
            };
            i= SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
			return i;
		}
		#endregion			
		
        #region##添加HW_District
        /// <summary>
        /// 功能：添加HW_District
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add(HW_District hw_district)
        {
            int result = 0;
            string sql = "insert into HW_District(DistrictCode,Name,Sort,ParentDistrictCode,Version,Level,SchoolNumber) values(@DistrictCode,@Name,@Sort,@ParentDistrictCode,@Version,@Level,@SchoolNumber)";			
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@DistrictCode",hw_district.DistrictCode),
				new SqlParameter("@Name",hw_district.Name),
				new SqlParameter("@Sort",hw_district.Sort),
				new SqlParameter("@ParentDistrictCode",hw_district.ParentDistrictCode),
				new SqlParameter("@Version",hw_district.Version),
				new SqlParameter("@Level",hw_district.Level),
				new SqlParameter("@SchoolNumber",hw_district.SchoolNumber)
            };
            result = SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
            return result;
        }
         /// <summary>
        /// 功能：添加HW_District
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add(SqlTransaction transaction,HW_District hw_district)
        {
            int result = 0;
            string sql = "insert into HW_District(DistrictCode,Name,Sort,ParentDistrictCode,Version,Level,SchoolNumber) values(@DistrictCode,@Name,@Sort,@ParentDistrictCode,@Version,@Level,@SchoolNumber)";			
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@DistrictCode",hw_district.DistrictCode),
				new SqlParameter("@Name",hw_district.Name),
				new SqlParameter("@Sort",hw_district.Sort),
				new SqlParameter("@ParentDistrictCode",hw_district.ParentDistrictCode),
				new SqlParameter("@Version",hw_district.Version),
				new SqlParameter("@Level",hw_district.Level),
				new SqlParameter("@SchoolNumber",hw_district.SchoolNumber)
            };
            result = SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
            return result;
        }
        #endregion
		
		#region##得到HW_District集合
		/// <summary>
        /// 功能：得到HW_District集合
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>       
        /// <returns></returns>
		public IList<HW_District> GetList()
		{
			string sql = "select * from HW_District";			
			SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString,CommandType.Text,sql);
            return MapperList(reader);
		}
		#endregion
		
		#region##修改HW_District
        /// <summary>
        /// 功能：修改HW_District
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int ModHW_District(HW_District hw_district)
        {
            int result = 0;
            string sql = "update HW_District set DistrictCode = @DistrictCode,Name = @Name,Sort = @Sort,ParentDistrictCode = @ParentDistrictCode,Version = @Version,Level = @Level,SchoolNumber = @SchoolNumber where DistrictCode = @DistrictCode";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@DistrictCode",hw_district.DistrictCode),
				new SqlParameter("@Name",hw_district.Name),
				new SqlParameter("@Sort",hw_district.Sort),
				new SqlParameter("@ParentDistrictCode",hw_district.ParentDistrictCode),
				new SqlParameter("@Version",hw_district.Version),
				new SqlParameter("@Level",hw_district.Level),
				new SqlParameter("@SchoolNumber",hw_district.SchoolNumber)
            };
            result = SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
            return result;
        }
         /// <summary>
        /// 功能：修改HW_District
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int ModHW_District(SqlTransaction transaction,HW_District hw_district)
        {
            int result = 0;
            string sql = "update HW_District set DistrictCode = @DistrictCode,Name = @Name,Sort = @Sort,ParentDistrictCode = @ParentDistrictCode,Version = @Version,Level = @Level,SchoolNumber = @SchoolNumber where DistrictCode = @DistrictCode";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@DistrictCode",hw_district.DistrictCode),
				new SqlParameter("@Name",hw_district.Name),
				new SqlParameter("@Sort",hw_district.Sort),
				new SqlParameter("@ParentDistrictCode",hw_district.ParentDistrictCode),
				new SqlParameter("@Version",hw_district.Version),
				new SqlParameter("@Level",hw_district.Level),
				new SqlParameter("@SchoolNumber",hw_district.SchoolNumber)
            };
            result = SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
            return result;
        }
        #endregion
		
		#region##统计HW_District
		/// <summary>
        /// 修改HW_District
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
		public int SelectCount()
		{
			int i = 0;
            string sql = "select count(*) from HW_District";
            i = (int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql);
            return i;
		}
		/// <summary>
        /// 修改HW_District
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int SelectCountById()
        {
            int i=0;
            string sql="select count(DistrictCode) from HW_District";
            i = (int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql);
            return i;
        }
		#endregion
        #region##自定义HW_District
        /// <summary>
        /// 到到用户所有授权可看到的机构用户
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="pdistrictCode"></param>
        /// <returns></returns>
        public IList<HW_District> GetSubList(string pdistrictCode)
        {
            string sql = @";WITH m(DistrictCode,NAME,ParentDistrictCode,Sort,Level,SchoolNumber) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode,R.Sort,R.Level,R.SchoolNumber FROM dbo.HW_District R WHERE DistrictCode=@districtcode
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode,child.Sort,child.Level,child.SchoolNumber FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
select * from  m";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@districtCode",pdistrictCode),
            };
            //DataTable result = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            //return result;
            return MapperList(SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql, sp));
        }
        /// <summary>
        /// 得到统计报表
        /// </summary>
        /// <param name="districtCode"></param>
        public DataSet GetDisCount(string districtCode,string yearcode)
        {
            string sql= @"
                SELECT a.DistrictCode,a.Name,SchoolNumber,COUNT(b.SchoollCode) acount,COUNT(c.UpYearCode) bcount,
CASE SchoolNumber WHEN 0 THEN str(0)+'%' ELSE Cast(round(Cast(COUNT(b.SchoollCode) as float)*100/SchoolNumber,2) as varchar(10))+'%' END 
pacount ,CASE SchoolNumber WHEN 0 THEN str(0)+'%' ELSE Cast(round(Cast(COUNT(c.UpYearCode) as float)*100/SchoolNumber,2) as varchar(10))+'%' END 
pbcount FROM dbo.HW_District a LEFT JOIN (SELECT * FROM  dbo.HW_School WHERE Permit='true') b ON a.DistrictCode=b.DistrictCode 
LEFT JOIN (select * from HW_SchoolUpLoadLog where upyearcode Like @yearcode) c ON b.SchoollCode=c.UpSchoolCode
WHERE a.ParentDistrictCode=@districtCode or a.districtcode=@districtcode GROUP BY a.DistrictCode,a.Name,SchoolNumber order by a.DistrictCode
            ";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@districtCode",districtCode),
                new SqlParameter("@yearcode",yearcode+"%"),
            };
            DataSet ds= SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);
            return ds;
        }
        public DataSet GetCityCount(string districtCode, string yearcode)
        {
            string sql = @"
                SELECT a.DistrictCode,a.Name,SchoolNumber,COUNT(b.SchoollCode) acount,COUNT(c.UpYearCode) bcount,
CASE SchoolNumber WHEN 0 THEN str(0)+'%' ELSE Cast(round(Cast(COUNT(b.SchoollCode) as float)*100/SchoolNumber,2) as varchar(10))+'%' END 
pacount ,CASE SchoolNumber WHEN 0 THEN str(0)+'%' ELSE Cast(round(Cast(COUNT(c.UpYearCode) as float)*100/SchoolNumber,2) as varchar(10))+'%' END 
pbcount FROM dbo.HW_District a LEFT JOIN (SELECT * FROM  dbo.HW_School WHERE Permit='true') b ON a.DistrictCode=b.cityid 
LEFT JOIN (select * from HW_SchoolUpLoadLog where upyearcode Like @yearcode) c ON b.SchoollCode=c.UpSchoolCode
WHERE a.ParentDistrictCode=@districtCode GROUP BY a.DistrictCode,a.Name,SchoolNumber
            ";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@districtCode",districtCode),
                new SqlParameter("@yearcode",yearcode+"%"),
            };
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);
            return ds;
        }
        #endregion
                
    }
}
